#!/usr/bin/env python
# -*- coding:utf-8 -*-  

######基本思路#####
#准备工作
# 1、定义好报告模板
# 2、在数据库中创建存放报告数据的表
# 开始敲代码
# 1、导包：pymysql用来与数据库建立联系
# 2、定义类HTMLReport
# 3、定义方法用来读取数据库的数据
# 4、将数据填充到报告模板中
#   4.1 打开模板
#   4.2 替换模板中的模板变量
# 5、将数据写入报告---生成报告

import pymysql
import os,time,random
from PIL import ImageGrab
from pymysql.cursors import DictCursor

class HTMLReport:
    def __init__(self):
        pass

    def generation(self,version):
        #与数据库建立连接
        conn = pymysql.connect(host='192.168.1.146',user='root',passwd='123456',db='Alpha',charset='utf8')
        #使用以字典类型返回结果集的游标
        cursor = conn.cursor(DictCursor)
        sql = "select * from report where version = '%s'" % version
        cursor.execute(sql)
        result_list = cursor.fetchall()
        print(result_list)
        #获取测试时间
        test_date = result_list[0]['testtime']
        #用例个数
        case_count = len(result_list)
        #用例执行结果
        sql = "SELECT result,count(*) as count from report where version = '%s' GROUP BY result" % version
        cursor.execute(sql)
        count_list = cursor.fetchall()
        # print(count_list)
        pass_count,fail_count,error_count = 0,0,0
        for item in count_list:
            if item['result'] == '失败':
                fail_count = item['count']
            elif item['result'] == '成功':
                pass_count = item['count']
            elif item['result'] == '错误':
                error_count = item['count']
        #最后时间
        last_time = result_list[len(result_list)-1]['testtime']
        #打开模板文件
        with open('./template.html',encoding='utf-8') as file:
            content = file.read()

        #替换模板内容
        content = content.replace('$test-date',str(test_date))
        content = content.replace('$test-version',version)
        content = content.replace('$pass-count',str(pass_count))
        content = content.replace('$fail-count',str(fail_count))
        content = content.replace('$error-count',str(error_count))
        content = content.replace('$last-time',str(last_time))
        content = content.replace('$case_count',str(case_count))
        # print(content)

        #将测试用例替换到模板中（替换$test-result变量）----核心
        test_result = ""
        for result in result_list:
            test_result += '<tr height="40">'
            test_result += '<td width="7%%">%s</td>' % result['id']
            test_result += '<td width="9%%">%s</td>' % result['module']
            test_result += '<td width="9%%">%s</td>' % result['testtype']
            test_result += '<td width="7%%">%s</td>' % result['caseid']
            test_result += '<td width="20%%">%s</td>' % result['casetitle']
            if result['result'] == '成功':
                test_result += '<td width="7%%">%s</td>' % result['result']
            elif result['result'] == '失败':
                test_result += '<td width="7%%" bgcolor="red">%s</td>' % result['result']
            elif result['result'] == '错误':
                test_result += '<td width="7%%" bgcolor="yellow">%s</td>' % result['result']
            test_result += '<td width="16%%">%s</td>' % result['testtime']
            test_result += '<td width="15%%">%s</td>' % result['error']
            test_result += '<td width="10%%"><a href="%s">%s</a></td>' % (result['screenshot'],result['screenshot'])
            test_result += '</tr>\n'
        content = content.replace('$test-result',test_result)
        print(content)

        #生成测试报告
        file_path = './template.html' + time.strftime('report_%Y%m%d_%H%M%S') + '.html'
        with open(file_path,mode='w+',encoding='utf-8') as file:
            file.write(content)

    #将用例执行结果写入到数据库中
    def write_database(self,version,module,testtype,caseid,casetitle,result,testtime,error,ecreenshot):
        conn = pymysql.connect(host='192.168.1.146', user='root', passwd='123456', db='woniucbt', charset='utf8')
        cursor = conn.cursor()
        sql = "insert into report(version,module,testtype,caseid,casetitle,result,testtime,error,ecreenshot) " \
              "values('%s','%s','%s','%s','%s','%s','%s','%s','%s')" \
              % (version, module, testtype, caseid, casetitle, result, testtime, error, ecreenshot)
        cursor.execute(sql)
        conn.commit()
        cursor.close()
        conn.close()

    #截图
    def pic_screenshot(self):
        pic_path = os.path.abspath('.') + '/report/'
        filename = time.strftime('%Y%m%d_%H%M%S.png')
        ImageGrab.grab().save(pic_path+filename)
        return filename

if __name__ == '__main__':
    report = HTMLReport()
    report.generation('3.5')
    #report.write_database('3.5','学员管理','GUI测试','TC_001','学员信息显示','成功','2019-05-28 12:01:15','无','无')